#! /usr/bin/env python
"""
sqlite helper functions
Copyright (c) 2011-2013 Christian T. Steigies <steigies@physik.uni-kiel.de>

This program is free software: you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.

This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.

You should have received a copy of the GNU General Public License
along with this program.  If not, see <http://www.gnu.org/licenses/>.
"""

__author__ = "Christian T. Steigies <steigies@physik.uni-kiel.de>"
__license__ = "GPL License"


## --------------------------------------------------------------------------
import os
import sys
import datetime
# SpaceWx uses CentOS5 with Python2.4 and pysqlite2
try:
    from pysqlite2 import dbapi2 as sqlite3
except ImportError:
    try:
        from sqlite3 import dbapi2 as sqlite3
    except ImportError:
        print "SQLite module not found"
        sys.exit(1)

from nmdb.tools.datetool import str2datetime
from nmdb.nmdb.sqlite_format import sql_create, sql_insert, sql_select, sql_print
#from nmdb.station.station_data import check_data, check_pressure


## --------------------------------------------------------------------------
class SqlData(object):
## TODO Merge StationData class!
    """data of station can be in filename or in :memory:
        data is stored in NMDB format!
    if station is empty, FAIL (no longer: read in all stations)
    read in 1h or 1m only?
    methods to return uncorrected, corrected (+eff?) and pressure
    methods to convert to NMDB specs: count per second
    """
    def __init__(self, filename):
        self.__conn = sqlite3.connect(filename)
        self._cursor = self.__conn.cursor()
        if filename == ':memory:':
            self._write = True
            self._cursor.execute(sql_create)
        else:
            self._write = False  # use for xyz2sql?

    def min(self):
        self._cursor.execute('''SELECT MIN(datetime) FROM data''')
        [timestamp] = self._cursor.fetchone()
        utc = str2datetime(timestamp)
        return utc

    def max(self):
        self._cursor.execute('''SELECT MAX(datetime) FROM data''')
        [timestamp] = self._cursor.fetchone()
        utc = str2datetime(timestamp)
        return utc

    def count(self):
        self._cursor.execute('''SELECT COUNT(*) FROM data''')
        [count] = self._cursor.fetchone()
        return count

    def insert(self, row):
        self._cursor.execute(sql_insert, row)

    def sql(self, year=2000, month=1, day=1, hour=0, minute=0, limit=1):
        """return the value from SQL table which matches the given data"""
        data = [datetime.datetime(year, month, day, hour, minute, 0), limit]
        command = sql_select + " WHERE datetime >= \"%s\" LIMIT %i" % (data[0], data[1])
        print "CMD:", command
        self._cursor.execute(command)
        value = []
        for row in self._cursor:
            value.append(row)
        self.show()
        return value

    def show(self, limit=1):
        # def show(self, year=2000, month=1, day=1, hour=0, minute=0, limit=1):
        """print data"""
        #value = print_sql(self, year, month, day, hour, minute, limit)
        #
        #return value
        command = "%s ORDER BY datetime LIMIT %i" % (sql_select, limit)
        print "SHOW", command
        self._cursor.execute(command)
        row = " "
        while row:
            row = self._cursor.fetchone()  # should return only one line, or None
        #for row in self._cursor:
            print row

    def value(self, year=2000, month=1, day=1, hour=0, minute=0):
        """return values which are stored in NMDB format"""
        data = [datetime.datetime(year, month, day, hour, minute, 0)]
        command = sql_select + " WHERE datetime == \"%s\"" % data
        self._cursor.execute(command)
        row = self._cursor.fetchone()  # should return only one line, or None
        if row:
            #(timestamp, uncorr, pressure, corr) = row
            (utc, interval, uncorr, corr_e, corr_p, pressure) = row
            #utc = str2datetime(timestamp)
            return (utc, interval, uncorr, corr_e, corr_p, pressure)
        return None

    def borkenvalue(self, year=2000, month=1, day=1, hour=0, minute=0):
        """convert values into NMDB format:
        counts are already given as counts per second
        pressure is given in torr: convert to mbar
        check_data|Pressure with optional range
        Check* returns NULL if input == 0 or out of range"""

        try:
            (utc, u, p, c) = self._value(year, month, day, hour, minute)
        except TypeError:
            return None

        if 0:
            uncorr = check_data(u, cr_min=10., cr_max=10000.)
            p_mbar = check_pressure(p, p_min=100., p_max=1500.)
            corr_p = check_data(c, cr_min=10., cr_max=10000.)
            corr_e = corr_p
        else:
            uncorr = u
            p_mbar = p
            corr_p = c
            corr_e = corr_p

        return (utc, uncorr, p_mbar, corr_p, corr_e)

    def generator(self, start=None):
        """return the next value from SQL table"""
        ## TODO define columns as variable
        if start is None:
            command = sql_select + " ORDER BY datetime"
        else:
            command = sql_select + " WHERE datetime > \"" + start + "\" ORDER BY datetime"
        #print command
        self._cursor.execute(command)
        for row in self._cursor:
            yield row


## --------------------------------------------------------------------------
def sqlfilename(datadir, name, mode="write", data="1m"):
    """
    name of sqlfile to store data in.
    all files are to be considered temporary
    """

    if data == "1m":
        sqlfile = datadir + "/" + name.upper() + "_1m" + ".sqlite"
    elif data == "1h":
        sqlfile = datadir + "/" + name.upper() + "_1h" + ".sqlite"
    else:
        print "unknown data:", data
        sys.exit(1)

    if mode == "write":
        if os.path.isfile(sqlfile):
            print "sqlite file already exists:", sqlfile
            print "please remove this file before running this script"
            sys.exit(1)

    if mode == "read":
        if not os.path.isfile(sqlfile):
            print "\nsqlite file does not exist:", sqlfile
            print "please create this file with bartol2sql/cvs2sql first."
            sys.exit(1)

    return sqlfile


## --------------------------------------------------------------------------
def read_sql(datadir, name, year=None):
    """read data from sqlite file"""

    sqlfile = sqlfilename(datadir, name, year, "read")
    sql_conn = sqlite3.connect(sqlfile)
    sql_cursor = sql_conn.cursor()

    command = sql_select + " ORDER BY datetime"
    sql_cursor.execute(command)

    row = " "
    data = []
    while row:
        row = sql_cursor.fetchone()  # should return only one line, or None
        data.append(row)

    return data


## --------------------------------------------------------------------------
def print_sql(data, year=2000, month=1, day=1, hour=0, minute=0, limit=1):
    """print data"""
    print "print_sql"
    for row in data.sql(year, month, day, hour, minute, limit):
#        print sql_print % (row)
        print sql_print

        return None

## --------------------------------------------------------------------------
if __name__ == "__main__":
    print "This is the sqlite helper module."